package pms.common.dao;

import java.io.ByteArrayInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import pms.common.value.sgSearchValue;

public class ListSGDaoImpl implements ListSGDao {
	private JdbcTemplate jdbcTemplate;
	
	public SqlRowSet getMasterData(String l_key) throws SQLException,
	InstantiationException, IllegalAccessException,
	ClassNotFoundException {
		SqlRowSet l_rs = null;
		String l_sql = "select * from TBL_MASTER "
		+ " where MASTER_GROUP = '"
		+ l_key + "' ORDER BY MASTER_ID";
		System.out.println("l_sql:" + l_sql);
		l_rs = jdbcTemplate.queryForRowSet(l_sql);
		return l_rs;
	}
	@SuppressWarnings("unchecked")
	public HashMap getProject() throws SQLException,
	InstantiationException, IllegalAccessException,
	ClassNotFoundException {
		HashMap l_hashMap = new HashMap();
		SqlRowSet l_rs = null;
		String l_sql = "select PROJECT_ID,PROJECT_NAME from tbl_project order by PROJECT_ID";
		System.out.println("l_sql:" + l_sql);
		l_rs = jdbcTemplate.queryForRowSet(l_sql);
		while(l_rs.next()) {
        	l_hashMap.put(l_rs.getString("PROJECT_ID").trim(), l_rs.getString("PROJECT_NAME").trim());
        }
		return l_hashMap;
	}
	@SuppressWarnings("unchecked")
	public HashMap getTheme() throws SQLException,
	InstantiationException, IllegalAccessException,
	ClassNotFoundException {
		HashMap l_hashMap = new HashMap();
		SqlRowSet l_rs = null;
		String l_sql = "select THEME_ID,THEME_NAME from tbl_theme order by theme_id";
		System.out.println("l_sql:" + l_sql);
		l_rs = jdbcTemplate.queryForRowSet(l_sql);
		while(l_rs.next()) {
			l_hashMap.put(l_rs.getString("THEME_ID").trim(), l_rs.getString("THEME_NAME").trim());
        }
		return l_hashMap;
	}
	@SuppressWarnings("unchecked")
	public List<HashMap> getUserData() throws SQLException,
	InstantiationException, IllegalAccessException,
	ClassNotFoundException {
		SqlRowSet l_rs = null;
		List<HashMap> l_array = new ArrayList();
		String l_sql = "select USER_NAME,USER_DISP_NAME from TBL_USER "
		+"ORDER BY USER_ID";
		System.out.println("l_sql:" + l_sql);
		l_rs = jdbcTemplate.queryForRowSet(l_sql);
       while(l_rs.next()) {
        	HashMap l_hashMap = new HashMap();
        	l_hashMap.put("user_name",l_rs.getString("USER_NAME").trim());
        	l_hashMap.put("user_disp_value",l_rs.getString("USER_DISP_NAME").trim());
        	l_array.add(l_hashMap);
        }
		return l_array;
	}
	@SuppressWarnings("unchecked")
	public List<HashMap> getFunctionArray() throws SQLException,
	InstantiationException, IllegalAccessException,
	ClassNotFoundException {
		SqlRowSet l_rs = null;
		List<HashMap> l_array = new ArrayList();
		String l_sql = "select THEME_ID,THEME_NAME from tbl_theme where del_fg=0 order by theme_id";
		System.out.println("l_sql:" + l_sql);
		l_rs = jdbcTemplate.queryForRowSet(l_sql);
       while(l_rs.next()) {
        	HashMap l_hashMap = new HashMap();
        	l_hashMap.put("theme_id",l_rs.getString("THEME_ID").trim());
        	l_hashMap.put("theme_name",l_rs.getString("THEME_NAME").trim());
        	l_array.add(l_hashMap);
        }
		return l_array;
	}
	
	
	public SqlRowSet getListData(sgSearchValue l_search) throws SQLException,
	InstantiationException, IllegalAccessException,
	ClassNotFoundException {
		// TODO Auto-generated method stub
		// boolean l_flg = false;
		SqlRowSet l_rs = null;
		String l_sql = "select ZH_NO,PROJECTID,TEMA,TEST_LVL,TEST_PART,TESTER,TEST_DATE,ZH_STATUS,MEMO,LIMIT_DATE,ZH_MODIFY,REASON "
			+ " from TBL_ZH_LIST where QA_DEL_FLAG = '0' ";
		// サブシステム
		if (!(l_search.getProjectId().equals("") || l_search.getProjectId()
				.equals(null))) {
			l_sql = l_sql + "and PROJECTID = '" + l_search.getProjectId()
				+ "' ";
			// l_flg = true;
		}
		// 所属テーマ
		if (!(l_search.getTema().equals("") || l_search.getTema().equals(null))) {
			l_sql = l_sql + "and TEMA = '" + l_search.getTema() + "' ";
			// l_flg = true;
		}

		// テスト段階
		if (!(l_search.getTest_lvl().equals("") || l_search.getTest_lvl()
				.equals(null))) {
			l_sql = l_sql + "and TEST_LVL = '" + l_search.getTest_lvl() + "' ";
			// l_flg = true;
		}
		// 検出側
		if (!(l_search.getTest_part().equals("") || l_search.getTest_part()
				.equals(null))) {
			l_sql = l_sql + "and TEST_PART = '" + l_search.getTest_part() + "' ";
			// l_flg = true;
		}
		// 検出者
		if (!(l_search.getTester().equals("") || l_search.getTester().equals(null))) {
			l_sql = l_sql + "and TESTER = '" + l_search.getTester() + "' ";
			// l_flg = true;
		}
		//修正担当
		if (!(l_search.getModify().equals("") || l_search.getModify().equals(null))) {
			l_sql = l_sql + "and ZH_MODIFY = '" + l_search.getModify() + "' ";
			// l_flg = true;
		}
		//ステータス
		if (!(l_search.getStatus().equals("") || l_search.getStatus().equals(null))) {
			l_sql = l_sql + "and ZH_STATUS = '" + l_search.getStatus() + "' ";
			// l_flg = true;
		}
		//障害要因
		if (!(l_search.getReason().equals("") || l_search.getReason().equals(null))) {
			l_sql = l_sql + "and REASON = '" + l_search.getReason() + "' ";
			// l_flg = true;
		}
		// 検出日
		if (!(l_search.getTestStartDate_Y().equals("") || l_search
				.getTestStartDate_Y().equals(null))
				&& !(l_search.getTestStartDate_M().equals("") || l_search
					.getTestStartDate_M().equals(null))
					&& !(l_search.getTestStartDate_D().equals("") || l_search
				.getTestStartDate_D().equals(null))) {
			l_sql = l_sql + "and TEST_DATE >= '"
				+ l_search.getTestStartDate_Y()
				+ l_search.getTestStartDate_M()
				+ l_search.getTestStartDate_D() + "' ";
			// l_flg = true;
		}
		// 検出日
		if (!(l_search.getTestEndDate_Y().equals("") || l_search
				.getTestEndDate_Y().equals(null))
				&& !(l_search.getTestEndDate_M().equals("") || l_search
						.getTestEndDate_M().equals(null))
						&& !(l_search.getTestEndDate_D().equals("") || l_search
								.getTestEndDate_D().equals(null))) {
			l_sql = l_sql + "and TEST_DATE <= '"
				+ l_search.getTestEndDate_Y() + l_search.getTestEndDate_M()
				+ l_search.getTestEndDate_D() + "' ";
			// l_flg = true;
		}
		//キー
		if (!(l_search.getKey().equals("") || l_search.getKey().equals(null))) {
			l_sql = l_sql + "and MEMO like '%" + l_search.getKey() + "%' " +
					"or ERROR_TEXT like '%"+ l_search.getKey() + "%' or QA_MEMO like '%" 
					+ l_search.getKey() + "%' or REASON like '%"+ l_search.getKey() + 
					"%' or REASONS like '%"+ l_search.getKey() + "%'";
			// l_flg = true;
		}
		l_sql = l_sql + "order by PROJECTID,TEMA ";
		l_rs = jdbcTemplate.queryForRowSet(l_sql);
		return l_rs;
	}
	public boolean deleteData(String[] banGos) {
		// TODO Auto-generated method stub
		boolean l_flg = false;
		String l_sql = "update TBL_ZH_LIST set QA_DEL_FLAG = '1' where ";
		for (String l_banGo : banGos) {
			if (l_flg) {
				l_sql = l_sql + "or ";
			}
			l_sql = l_sql + "ZH_NO = '" + l_banGo + "' ";
			l_flg = true;
		}
		int l_rs = jdbcTemplate.update(l_sql);
		if (l_rs < 0) {
			return false;
		} else {
			return true;
		}
	}

	public InputStream expData(String[] banGos) throws Exception {
		String sr[] = new String[18];
		String title[] = new String[18];
		short length[] = new short[18];
		// TITLE名
		title[0] = "番号";
		title[1] = "プロジェクト";
		title[2] = "所属テーマ";
		title[3] = "テスト段階";
		title[4] = "検出側";
		title[5] = "検出者";
		title[6] = "検出日";
		title[7] = "ステータス";
		title[8] = "概要";
		title[9] = "障害内容";
		title[10] = "対応期限";
		title[11] = "備考";
		title[12] = "修正担当側";
		title[13] = "修正担当";
		title[14] = "対応策";
		title[15] = "障害要因";
		title[16] = "原因";
		title[17] = "修正日";
		// wookBookの列の幅
		length[0] = 8;
		length[1] = 12;
		length[2] = 12;
		length[3] = 12;
		length[4] = 12;
		length[5] = 12;
		length[6] = 12;
		length[7] = 12;
		length[8] = 24;
		length[9] = 48;
		length[10] = 12;
		length[11] = 48;
		length[12] = 12;
		length[13] = 12;
		length[14] = 12;
		length[15] = 48;
		length[16] = 48;
		length[17] = 12;
		// wookbookを生成
		HSSFWorkbook wb = new HSSFWorkbook();
		// sheetを生成
		HSSFSheet sheet = wb.createSheet("new sheet");
		// 書式設定
		HSSFCellStyle style = wb.createCellStyle();
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
		HSSFRow rowTitle = sheet.createRow(0);
		int j = 1;
		for (int a = 0; a < banGos.length; a++) {
			SqlRowSet l_value = getExpData(banGos[a]);
			HSSFRow row = sheet.createRow(j);
			j++;
			while (l_value.next()) {
				sr[0] = l_value.getString("ZH_NO");
				sr[1] = l_value.getString("PROJECTID");
				sr[2] = l_value.getString("TEMA");
				sr[3] = l_value.getString("TEST_LVL");
				sr[4] = l_value.getString("TEST_PART");
				sr[5] = l_value.getString("TESTER");
				sr[6] = l_value.getString("TEST_DATE");
				sr[7] = l_value.getString("ZH_STATUS");
				sr[8] = l_value.getString("MEMO").replaceAll("&lt;br&gt",
				"\r\n");
				sr[9] = l_value.getString("ERROR_TEXT").replaceAll("&lt;br&gt",
				"\r\n");
				sr[10] = l_value.getString("LIMIT_DATE");
				sr[11] = l_value.getString("QA_MEMO").replaceAll("&lt;br&gt",
				"\r\n");
				sr[12] = l_value.getString("MODIFY_PART");
				sr[13] = l_value.getString("ZH_MODIFY");
				sr[14] = l_value.getString("MEHOOD").replaceAll("&lt;br&gt",
				"\r\n");
				sr[15] = l_value.getString("REASON");
				sr[16] = l_value.getString("REASONS").replaceAll("&lt;br&gt",
				"\r\n");
				sr[17] = l_value.getString("EDIT_DATE");
				for (int i = 0; i < 18; i++) {
					// CellのVALUEを設定
					HSSFCell cellTitle = rowTitle.createCell((short) i);
					cellTitle.setCellValue(title[i]);
					cellTitle.setCellStyle(style);
					sheet.setColumnWidth((short) i, (short) (length[i] * 256));
					HSSFCell cell = row.createCell((short) i);
					// 日付の書式を設定
					Workbook formatbook = new Workbook();
					HSSFDataFormat format = new HSSFDataFormat(formatbook);
					style.setDataFormat(format.getFormat("m/d/yy"));
					cell.setCellStyle(style);
					cell.setCellValue(sr[i]);
				}
			}
		}
		InputStream excelStream = new ByteArrayInputStream(wb.getBytes());
		return excelStream;
	}
	public InputStream expExcelData(String[] banGos) throws Exception {
		InputStream excelStream;
        StringBuffer excelBuf = new StringBuffer();
        excelBuf.append("番号\t");
        excelBuf.append("プロジェクト\t");
        excelBuf.append("所属テーマ\t");
        excelBuf.append("テスト段階\t");
        excelBuf.append("検出側\t");
        excelBuf.append("検出者\t");
        excelBuf.append("検出日\t");
        excelBuf.append("ステータス\t");
        excelBuf.append("概要\t");
        excelBuf.append("障害内容\t");
        excelBuf.append("対応期限\t");
        excelBuf.append("備考\t");
        excelBuf.append("修正担当側\t");
        excelBuf.append("修正担当\t");
        excelBuf.append("対応策\t");
        excelBuf.append("障害要因\t");
        excelBuf.append("原因\t");
        excelBuf.append("修正日\n");
        
        for (int a = 0; a < banGos.length; a++) {
			SqlRowSet l_value = getExpData(banGos[a]);
			while (l_value.next()) {
				excelBuf.append(l_value.getString("ZH_NO")+"\t");
				excelBuf.append(l_value.getString("PROJECTID")+"\t");
				excelBuf.append(l_value.getString("TEMA")+"\t");
				excelBuf.append(l_value.getString("TEST_LVL")+"\t");
				excelBuf.append(l_value.getString("TEST_PART")+"\t");
				excelBuf.append(l_value.getString("TESTER")+"\t");
				excelBuf.append(l_value.getString("TEST_DATE")+"\t");
				excelBuf.append(l_value.getString("ZH_STATUS")+"\t");
				excelBuf.append("\"" + l_value.getString("MEMO").replaceAll("&lt;br&gt",
				"\r\n") + "\"" +"\t");
				excelBuf.append(l_value.getString("ERROR_TEXT").replaceAll("&lt;br&gt",
				"\r\n")+"\t");
				excelBuf.append(l_value.getString("LIMIT_DATE")+"\t");
				excelBuf.append("\"" + l_value.getString("QA_MEMO").replaceAll("&lt;br&gt",
				"\r\n") + "\"" +"\t");
				excelBuf.append(l_value.getString("MODIFY_PART")+"\t");
				excelBuf.append(l_value.getString("ZH_MODIFY")+"\t");
				excelBuf.append("\"" + l_value.getString("MEHOOD").replaceAll("&lt;br&gt",
				"\r\n") + "\"" +"\t");
				excelBuf.append(l_value.getString("REASON")+"\t");
				excelBuf.append("\"" + l_value.getString("REASONS").replaceAll("&lt;br&gt",
				"\r\n") + "\"" +"\t");
				excelBuf.append(l_value.getString("EDIT_DATE")+"\n");
			}
		}
        String excelString = excelBuf.toString();
        excelStream = new ByteArrayInputStream(excelString.getBytes());
        return excelStream;
	}
	
	private SqlRowSet getExpData(String key) {
		SqlRowSet l_rs = null;
		String l_sql = "select ZH_NO,PROJECTID,TEMA,TEST_LVL,TEST_PART,TESTER,TEST_DATE,ZH_STATUS,MEMO,ERROR_TEXT,LIMIT_DATE,QA_MEMO,MODIFY_PART,ZH_MODIFY,MEHOOD,REASON,REASONS,EDIT_DATE "
				+ " from TBL_ZH_LIST where QA_DEL_FLAG = '0' and ZH_NO = '"
				+ key + "'";
		l_rs = jdbcTemplate.queryForRowSet(l_sql);
		return l_rs;
	}
	
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}


	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
}

